﻿using ExcelDataReader;
using ExeclHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace UpdateData.Common
{
    public class ReaderTableByCell
    {
        /// <summary>
        /// 读取Execl每个cell中的值返回
        /// </summary>
        /// <param name="filePath">文件位置</param>
        /// <param name="tableLocation">cell的位置</param>
        /// <returns></returns>
        public List<string> ExeclCellsReader(string filePath, List<TableLocation> tableLocations)
        {
            List<string> infoList = new List<string>();

            DataSet ds;
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var extension = Path.GetExtension(filePath).ToLower();
                IExcelDataReader reader = null;
                if (extension == ".xls")
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (extension == ".xlsx")
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                else if (extension == ".csv")
                {
                    reader = ExcelReaderFactory.CreateCsvReader(stream);
                }

                if (reader == null)
                    return null;

                using (reader)
                {
                    ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                       UseColumnDataType=true
                    });
                }

                var tablenames = GetTablenames(ds.Tables);
                if (ds.Tables.Count < 1)
                    return null;

                //取第一个table
                var dTable = ds.Tables[0];


                foreach (var item in tableLocations)
                {
                    string cellValue = dTable.Rows[item.RowIndex].ItemArray[item.ColumnIndex].ToString();
                    infoList.Add(cellValue);
                }

                //int index = 0;


              
                //foreach (DataRow row in dTable.Rows)
                //{
                //    foreach (var item in tableLocations)
                //    {
                //        if (index == item.RowIndex)
                //        {
                //            string cellValue = row.ItemArray[item.ColumnIndex].ToString().Trim();
                //            infoList.Add(cellValue);
                //        }
                //    }
                //    index++;
                //}
            }

            return infoList;
        }

        public IList<string> GetTablenames(DataTableCollection tables)
        {
            var tableList = new List<string>();
            foreach (var table in tables)
            {
                tableList.Add(table.ToString());
            }

            return tableList;
        }
    }

    /// <summary>
    /// 表格所在位置
    /// </summary>
    public class TableLocation
    {
        public int RowIndex { get; set; }

        public int ColumnIndex { get; set; }
    }
}
